package com.ss.competition.databaseadapter;

import Datas.Data;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class DBAdapter
{
	public static final String KEY_DATAID = "dataID";
	public static final String KEY_PICID = "picID";
	public static final String KEY_PICTURE = "picture";
	public static final String KEY_DATE = "date";
	public static final String KEY_TIME = "time";
	public static final String KEY_PURPOSE = "purpose";
	public static final String KEY_EXPENSE = "expense";
	public static final String KEY_REMARK = "remark";
	public static final String KEY_RECDATE = "recDate"; // recording date
	public static final String KEY_RECTIME = "recTime"; // recording time
	private static final String TAG = "DBAdapter";
	private static final String DATABASE_NAME = "BillMark";
	private static final String DATABASE_TABLE = "titles";
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_CREATE = "create table titles ("
			+ KEY_DATAID + " integer not null, " + KEY_PICID + " integer not null, "
			+ KEY_DATE + " text not null, " + KEY_TIME + " text not null, "
			+ KEY_PURPOSE + " text not null, " + KEY_EXPENSE
			+ " real not null, " + KEY_REMARK + " text, " + KEY_RECDATE
			+ " text not null, " + KEY_RECTIME + " text not null, "
			+ KEY_PICTURE + " blob);";
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DBAdapter(Context ctx)
	{
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper
	{
		DatabaseHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS titles");
			onCreate(db);
		}
	}


	public DBAdapter open() throws SQLException
	{
		db = DBHelper.getWritableDatabase();
		return this;
	}


	public void close()
	{
		DBHelper.close();
	}


	public long insertTitle(Data data)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_DATAID, data.dataID);
		initialValues.put(KEY_PICID, data.picID);
		initialValues.put(KEY_PICTURE, data.getBitmap());
		initialValues.put(KEY_DATE, data.date);
		initialValues.put(KEY_TIME, data.time);
		initialValues.put(KEY_PURPOSE, data.purpose);
		initialValues.put(KEY_EXPENSE, data.expense);
		initialValues.put(KEY_REMARK, data.remark);
		initialValues.put(KEY_RECDATE, data.recordingDate);
		initialValues.put(KEY_RECTIME, data.recordingTime);
		 Log.v("haha", data.date.toString());
		return db.insert(DATABASE_TABLE, null, initialValues);
	}

	public boolean deleteTitle(int dataID)
	{
		String[] s = new String[1];
		s[0]=dataID+"";
		return db.delete(DATABASE_TABLE, KEY_DATAID + "=?", s) > 0;
	}


	public Cursor getAllTitles()
	{
		return db.query(DATABASE_TABLE, null, null, null, null, null, null);
	}

	public Cursor getTitleByRemark(String remark)
	{
		String[] s = new String[1];
		Cursor mCursor;
		s[0] = remark;
		mCursor = db.query(true, DATABASE_TABLE, null,
							KEY_REMARK + "=?", s, null, null,
				null, null);
		if (mCursor != null)
		{
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getTitle(String event) throws SQLException
	{
		String[] s = new String[1];
		Cursor mCursor;
		s[0] = event;
		mCursor = db.query(true, DATABASE_TABLE, null,
							KEY_PURPOSE + "=?", s, null, null,
				null, null);

		if (mCursor != null)
		{
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	public Cursor getTitle(String start, String end) throws SQLException
	{
		Cursor mCursor;
		mCursor = db.rawQuery("SELECT * FROM "+DATABASE_TABLE+" WHERE "
				+KEY_DATE+">=? AND "+KEY_DATE+"<=?", new
				String[]{start, end});
		if (mCursor != null)
		{
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	public Cursor getTitle(Double start, Double end)
	{
		Cursor mCursor;
		mCursor = db.rawQuery("SELECT * FROM "+DATABASE_TABLE+" WHERE "
				+KEY_EXPENSE+">=? AND "+KEY_EXPENSE+"<=?", new
				String[]{start.toString(), end.toString()});
		if (mCursor != null)
		{
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public void deleteAll()
	{
		db.execSQL("DROP TABLE IF EXISTS titles");
	}

	public void create()
	{
		db.execSQL(DATABASE_CREATE);
	}

}
